package com.sdgs.app.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
//import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel相关工具类.
 *
 * @author yinyanzhen
 */
public final class ExcelUtil {
    
    /**
     * Creates a new instance of ExcelUtil.
     */
    private ExcelUtil() {
    }
    
    /**
     * 实体类结果集导出excel(方法当前对date类型进行了格式化处理).
     *
     * @author yinyanzhen
     * @param dataList 实体结果集
     * @param clazz 实体类Class
     * @param header 列标题
     * @param dataProperty 列属性
     * @return HSSFWorkbook
     * @throws Exception Exception
     * @since V1.0.0
     */
    public static HSSFWorkbook drawClassListExcel(List<Object> dataList, Class clazz, 
                String[] header, String[]  dataProperty)throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        //excel内,sheet页名称
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 样式对象
        HSSFCellStyle styleHeader = wb.createCellStyle();
        // 字体
        HSSFFont fHeader = wb.createFont();
        // 加粗
        fHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 水平居中
        styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleHeader.setWrapText(true);
        styleHeader.setFont(fHeader);

        // 标题行
        HSSFRow rowHeaer = sheet.createRow((short) 0);

        // 1、先输出表格头
        for (int i = 0; i < header.length; i++) {
            HSSFCell cell = rowHeaer.createCell(i);
            cell.setCellValue(new HSSFRichTextString(header[i]));
            cell.setCellStyle(styleHeader);
        }
        // 2、输出内容
        // 数据行索引从1开始
        short i = 1;
        for (Object obj : dataList) {
            // 数据行
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < dataProperty.length; j++) {
                String methodName = "get" + dataProperty[j].substring(0, 1).toUpperCase() 
                    + dataProperty[j].substring(1);
                Method method = clazz.getMethod(methodName);
                String value = (method.invoke(obj) == null ? "" : method.invoke(obj).toString());
                if (!"".equals(value)) {
                    //获取参数类型,是时间类型时,对时间进行格式化
                    Type type = method.getGenericReturnType();
                    if ("java.util.Date".equals(type.getTypeName())) {
                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        Date date = new Date(value);
                        value = format.format(date);
                    }
                }
                
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(new HSSFRichTextString(value));
            }
            i++;
        }
        return wb;
    }
    
    /**
     * Map结果集,导出excel.
     * 
     * @author yinyanzhen
     * @param dataList map结果集
     * @param header 列名
     * @param dataProperty 列属性
     * @return HSSFWorkbook
     * @since V1.0.0
     */
    public static HSSFWorkbook drawMapListExcel(List<Map> dataList, String[] header, String[]  dataProperty) {
        HSSFWorkbook wb = new HSSFWorkbook();
        //excel内,sheet页名称
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 样式对象
        HSSFCellStyle styleHeader = wb.createCellStyle();
        // 字体
        HSSFFont fHeader = wb.createFont();
        // 加粗
        fHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 水平居中
        styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleHeader.setWrapText(true);
        styleHeader.setFont(fHeader);

        // 标题行
        HSSFRow rowHeaer = sheet.createRow((short) 0);

        // 1、先输出表格头
        for (int i = 0; i < header.length; i++) {
            HSSFCell cell = rowHeaer.createCell(i);
            cell.setCellValue(new HSSFRichTextString(header[i]));
            cell.setCellStyle(styleHeader);
        }
        // 2、输出内容
        // 数据行索引从1开始
        short i = 1;
        for (Map uMap : dataList) {
            // 数据行
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < dataProperty.length; j++) {
                HSSFCell cell = row.createCell(j);
                String tmp = String.valueOf(uMap.get(dataProperty[j]));
                if (tmp == null || "null".equals(tmp)) {
                    tmp = "";
                }
                cell.setCellValue(new HSSFRichTextString(tmp));
            }
            i++;
        }
        return wb;
    }
    
    /**
     * 
     * getExcelMapList:excel文件,获取Map数据集. <br/>
     *
     * @author yinyanzhen
     * @param multipartFile multipartFile
     * @param propertys propertys
     * @return List
     * @throws Exception Exception
     * @since V1.0.0
     */
    public static List<Map> getExcelMapList(MultipartFile multipartFile, String[] propertys)throws Exception {
        //文件校验,读取
        Workbook workBook = getWorkBook(multipartFile);
        
        List list = new ArrayList();
        Sheet sheet = workBook.getSheetAt(0);
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            Map map = new HashMap();
            if (row != null) {
                for (int i = 0; i < propertys.length; i++) {
                   map.put(propertys[i], row.getCell(i));
                }
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 
     * getExcelClassList:excel文件获取bean对象list. <br/>
     *
     * @author yinyanzhen
     * @param multipartFile multipartFile
     * @param clazz clazz
     * @param propertys propertys
     * @return List
     * @throws Exception Exception
     * @since V1.0.0
     */
    public static List getExcelClassList(MultipartFile multipartFile, Class clazz,
        String[] propertys)throws Exception {
        //文件校验,读取
        Workbook workBook = getWorkBook(multipartFile);
        
        List list = new ArrayList();
        Sheet sheet = workBook.getSheetAt(0);
        
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            Object obj = clazz.newInstance();
            if (row != null) {
                for (int i = 0; i < propertys.length; i++) {
                    String getMethod = "get" + propertys[i].substring(0, 1).toUpperCase() + propertys[i].substring(1);
                    Type type = clazz.getMethod(getMethod).getGenericReturnType();
                    
                    String methodName = "set" + propertys[i].substring(0, 1).toUpperCase() + propertys[i].substring(1);
                    //属性类型是时间格式时,需要格式化为Date类型
                    if ("java.util.Date".equals(type.getTypeName())) {
                        Method method = clazz.getMethod(methodName, Date.class);
                        method.invoke(obj, row.getCell(i).getDateCellValue());
                    } else {
                        Method method = clazz.getMethod(methodName, String.class);
                        method.invoke(obj, row.getCell(i).getStringCellValue());
                    }
                }
                list.add(obj);
            }
        }
        
        return list;
    }
    
    /**
     * 
     * getWorkBook:excel导入,校验及转换方法. <br/>
     *
     * @author yinyanzhen
     * @param multipartFile multipartFile
     * @return Workbook
     * @throws Exception Exception
     * @since V1.0.0
     */
    public static   Workbook getWorkBook(MultipartFile multipartFile)throws Exception {
        // 截取文件后缀进行判断
        String suffix = multipartFile.getOriginalFilename().substring(
            multipartFile.getOriginalFilename().lastIndexOf(".")).toLowerCase();
        //格式判定
        if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
            throw new Exception("导入失败,请导入有效的excel文件!");
        }
        //非空判定
        if (multipartFile.isEmpty()) {
            throw new Exception("导入失败,文件不可为空!");
        }
        
        Workbook workBook = null;
        if (".xls".equals(suffix)) {
            workBook = new HSSFWorkbook(multipartFile.getInputStream());
        }
        if (".xlsx".equals(suffix)) {
//            workBook = new XSSFWorkbook(multipartFile.getInputStream());
        }

        return workBook;
    }

    /**
     * 
     * getSheetClassList:将sheet数据转为为实体类集合
     *
     * @param sheet
     * @param clazz
     * @param propertys
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws SecurityException 
     * @throws NoSuchMethodException 
     * @since V1.0.0
     */
    public static List  getSheetClassList(Sheet sheet, Class clazz, String[] propertys) throws Exception  {
        
        List list = new ArrayList();
        
        for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            Object obj = clazz.newInstance();
            if (row != null) {
                for (int i = 0; i < propertys.length; i++) {
                    String getMethod = "get" + propertys[i].substring(0, 1).toUpperCase() + propertys[i].substring(1);
                    Type type = clazz.getMethod(getMethod).getGenericReturnType();
                    
                    String methodName = "set" + propertys[i].substring(0, 1).toUpperCase() + propertys[i].substring(1);
                    //属性类型是时间格式时,需要格式化为Date类型
                    if ("java.util.Date".equals(type.getTypeName())) {
                        Method method = clazz.getMethod(methodName, Date.class);
                        method.invoke(obj, row.getCell(i).getDateCellValue());
                    } else {
                        Method method = clazz.getMethod(methodName, String.class);
                        
                          if (row.getCell(i)!=null) {
                            
                            if (0 == row.getCell(i).getCellType()) {   
                                //判断是否为日期类型  
                                    if(HSSFDateUtil.isCellDateFormatted(row.getCell(i))){  
                                        //用于转化为日期格式
                                        Date d = row.getCell(i).getDateCellValue();  
                                        SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd");  
                                        String  date= formater.format(d);  
                                        method.invoke(obj,date);
                                    }
                            }else{
                                row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                                method.invoke(obj,row.getCell(i)==null? null: row.getCell(i).getStringCellValue());
                                
                            }
                        } 
                    }
                }
                list.add(obj);
            }
        }
        
        return list;
        
    }
}
